﻿CREATE PROCEDURE [dbo].[spCreateSequenceStepsForOrder]
	@SequenceID int, 
	@SequenceOrderID int,
	@PrevSequenceStepID int,
	@RootPrevSequenceStepID int
AS
begin
	if @SequenceOrderID is null
		RAISERROR( 'spCreateSequenceStepsForOrder was called with a NULL SequenceOrderID', 18, -1 )

	declare cSequenceStepCursor cursor local for
	select SequenceStepID from tblSequenceStep
	where SequenceID = @SequenceID
		and coalesce( PrevSequenceStepID, -1 ) = coalesce(@RootPrevSequenceStepID, -1)
		and SequenceOrderID is null

	declare @RootSequenceStepID int
	open cSequenceStepCursor
	fetch next from cSequenceStepCursor into @RootSequenceStepID
	while @@FETCH_STATUS = 0
	begin
		declare @SequenceStepID int

		insert into tblSequenceStep ( SequenceID, SequenceOrderID, Delay, RecurrenceTerm, RecurrenceCount, RecurrenceCycle, PrevSequenceStepID, SourceSequenceStepID, CurrentCount, NextTriggerDate, LastOrderID )
		select @SequenceID, @SequenceOrderID, Delay, RecurrenceTerm, RecurrenceCount, RecurrenceCycle, @PrevSequenceStepID, @RootSequenceStepID, null, null, null
		from tblSequenceStep
		where SequenceStepID = @RootSequenceStepID

		set @SequenceStepID = SCOPE_IDENTITY()

		insert into tblSequenceStepProduct ( SequenceStepID, Qty, Price, ProductID )
		select @SequenceStepID, qty, price, ProductID
		from tblSequenceStepProduct where SequenceStepID = @RootSequenceStepID

		exec spCreateSequenceStepsForOrder @SequenceID, @SequenceOrderID, @SequenceStepID, @RootSequenceStepID

		fetch next from cSequenceStepCursor into @RootSequenceStepID
	end
	close cSequenceStepCursor
	deallocate cSequenceStepCursor
end
